package iptux;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import baseclass.MsgHistory;
import baseclass.PalInfo;

/**
 * This class stores the  chat history to sqlite database, and load the chat history
 * from the database. 
 * @author cwll<cwll2009@126.com>
 *
 */
public class MSGLogger {
	Connection conn;
	Statement stat;
	
	public MSGLogger() {
		try {
			// 连接SQLite的JDBC
			Class.forName("org.sqlite.JDBC");
			// 建立一个数据库名iptux_msg_log的连接，如果不存在就在当前目录下创建之
			conn = DriverManager.getConnection("jdbc:sqlite:iptux_msg_log.db");
			stat = conn.createStatement();
			if (!tabbleIsExist("msg_history")) {
				stat.executeUpdate("create table msg_history("
						+ "ID INTEGER PRIMARY KEY AUTOINCREMENT, "
						+ "log_Time TIMESTAMP default (datetime('now', 'localtime')),"
						+ "msg_Type int not null, "
						+ "msg text not null, "
						+ "pal_IP char(16) not null, "
						+ "pal_Name text not null "
						+ ");");
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	protected void finalize(){
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
     }

	/**
	 * Test is a table exist in the database.
	 * @param tableName
	 * @return
	 */
	public boolean tabbleIsExist(String tableName) {
		if (tableName == null) {
			return false;
		}
		try {
			String sql = "SELECT count(*) FROM sqlite_master WHERE type = \"table\" AND name = \"msg_history\"";
			ResultSet rs = stat.executeQuery(sql);
			int rowCount = rs.getInt(1);
			if(rowCount>0){
				return true;
			}
		} catch (Exception e) {
			return false;
		}
		return false;
	}
	
	/**
	 * Add a chat message to database.
	 * @param msgType
	 * @param msg
	 * @param pal
	 */
	public void addLogger(int msgType,String msg,PalInfo pal){
		SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String datetime = dateformat.format(new Date());
		String sql = "INSERT INTO msg_history VALUES (null,'"
				+ datetime + "','" + 
			String.valueOf(msgType) + "','" + msg + "','" +
			pal.ipv4 + "','" + pal.name + "');" ;
		try {
			stat.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Clear the chat history of a pal.
	 * @param pal
	 */
	public void clearPalLogger(PalInfo pal){
		String sql = "delete from msg_history where pal_IP='" +
				pal.ipv4 + "';";
		try {
			stat.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Clear all chat history .
	 * @param pal
	 */
	public void clearAllLogger(){
		String sql = "delete from msg_history" ;
		try {
			stat.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Read chat history from database of a pal, the pal is specified by IP address.
	 * @param palIP
	 * @return a array list stores the chat histories.
	 */
	public ArrayList<MsgHistory> getLogger(String palIP){
		ArrayList<MsgHistory> history = new ArrayList<MsgHistory>();
		ResultSet rs;
		String sql = "select * from msg_history where pal_IP='" +
				palIP + "' ORDER BY ID;";
		try {
			rs = stat.executeQuery(sql);
			while(rs.next()){
				MsgHistory item = new MsgHistory();
				item.ID = rs.getInt("ID");
				item.msgType  = rs.getInt("msg_Type");
				item.msg = rs.getString("msg");
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				item.dateTime = format.parse(rs.getString("log_Time"));
				history.add(item);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return history;
	}
}
